import java.io.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.servlet.*;
import javax.servlet.http.*;

import javax.swing.JOptionPane;

public class editItemInDB extends HttpServlet
{	
	public void doPost(HttpServletRequest req, HttpServletResponse rsp)
	  throws ServletException, IOException 
	{
        String tablename = req.getParameter("table");
		   
		try 
		{
		    //initialises connection to database and associated tools
			Statement stmt;
		    Class.forName("com.mysql.jdbc.Driver");
		    String url = "jdbc:mysql://localhost:3306/ideas_i";
		    Connection con = DriverManager.getConnection(url, "root", "ideas_inventory");

		    //defines the stmt variable as a statement of the current connection
		    stmt = con.createStatement();
		    
		    //if the table is people (special case)
		    if (tablename.equals("people"))
		    {
		    	//get parameters sent by the form
		    	String name = req.getParameter("form_name");
		    	String office = req.getParameter("form_office");
		    	String ext = req.getParameter("form_ext");
		    	String email = req.getParameter("form_email");
		    	
		    	//construct the SQL statement string to append the parameter values
		    	String executionString = "INSERT INTO "+ tablename + " VALUES (\"" + name + "\", \"" + office + "\", \"" + ext + "\", \"" + email + "\");";
		    	
		    	//execute the statement string
		    	stmt.executeUpdate(executionString);
		    }
		    //otherwise it is a generic table
		    else
		    {
		    	//get parameters sent by the form
		    	String item = req.getParameter("form_item");
				String owner = req.getParameter("form_owner");
				
				//construct the SQL statement string to append the parameter values
		    	String executionString = "INSERT INTO "+ tablename + " VALUES (\"" + item + "\", \"" + owner + "\");";

		    	//execute the statement string
		    	stmt.executeUpdate(executionString);  
		    }
		}
		//error handling currently not working
		catch (Exception e)
		{
			JOptionPane.showMessageDialog(null, "You have triggered an input error:" + e);
		}
	}
	public void doGet (HttpServletRequest req, HttpServletResponse rsp)
	  throws ServletException, IOException
	{
		//gets the initial parameter and initialises the response
		String tablename = req.getParameter("table");
		rsp.setContentType("text/html");
		PrintWriter out = rsp.getWriter();
		try 
	    {
			//initialises connection to database and associated tools
	        Statement stmt;
	        Class.forName("com.mysql.jdbc.Driver");
	        String url = "jdbc:mysql://localhost:3306/ideas_i";
	        Connection con = DriverManager.getConnection(url, "root", "ideas_inventory");

	        //defines the stmt variable as a statement of the current connection
	        stmt = con.createStatement();
	        //selects an initial Resultset used by all tables based on the 
	        //given table parameter
	        ResultSet result = stmt.executeQuery("SELECT * FROM " + tablename + ";");
	        
	        //if the table is people (special case)
	        if (tablename.equals("people"))
	        {
	        	String fn = req.getParameter("function");
	        	//if the function is to create a baseTable
	        	if (fn.equals("baseTable"))
	        	{
	        		//print the table headers
		        	out.println("<th class=\"records\"><b>Name</b></th>");
		        	out.println("<th class=\"records\"><b>Office Location</b></th>");
		        	out.println("<th class=\"records\"><b>UW Extension</b></th>");
		        	out.println("<th class=\"records\"><b>Email Address</b></th>");
		        	
		        	//loop through the resultset to construct the contents of the table
		        	while(result.next())
		        	{
		        		printRow(result, out, 4);
		        	}
	        	}
	        	//otherwise the function is a query
	        	else
	        	{
	        		//returns info about the queried name, currently unused except in checking
	        		String inName = req.getParameter("name");
	        		ResultSet info = stmt.executeQuery("SELECT * FROM people WHERE name=\"" + inName + "\";");
	        		//checks if the name actually exists in the people table
	        		if(info != null)
	        		{
		        		//constructs a reusable SQL statement string for all tables
	        			String qryStmt1 = "Select product_name FROM ";  
		        		String qryStmt2 = " WHERE owner_name=\"" + inName + "\";";
		        		
		        		out.println("<tr>");
		        		out.println("<th class=\"records\">Item Type</th>");
		        		out.println("<th class=\"records\">Item Name</th>");
		        		out.println("</tr>");
		        		
		        		
		        		//queries the software table
		        		ResultSet resSoft = stmt.executeQuery(qryStmt1 + "software" + qryStmt2);
		        		
		        		//prints results of the query in table form
		        		while(resSoft.next())
		        		{
		        			printQueryRow(resSoft, out, 1, "software");
		        		}
		        		
		        		//queries the books table
		        		ResultSet resBook = stmt.executeQuery(qryStmt1 + "books" + qryStmt2);
		        		
		        		//prints results of the query in table form
		        		while(resBook.next())
		        		{
		        			printQueryRow(resBook, out, 1, "book");
		        		}
		        		
		        		//queries the hardware table
		        		ResultSet resHard = stmt.executeQuery(qryStmt1 + "hardware" + qryStmt2);
		        		
		        		//prints results of the query in table form
		        		while(resHard.next())
		        		{
		        			printQueryRow(resHard, out, 1, "hardware");
		        		}
	        		}
	        		//otherwise indicate that the name does not match any item entries
	        		else
	        		{
	        		    out.println("<tr><td>There are no matches for that name</td></tr>");
	        		}
	        	}
	        }
	        
	        //otherwise it is a generic table
	        else
	        {
	        	//print table headers
		        out.println("<tr>");
		        out.println("<th class=\"records\"><b>" + tablename.substring(0,1).toUpperCase() + tablename.substring(1) + " Title</b></th>");
		        out.println("<th class=\"records\"><b>Owner's Name</b></th>");
		        out.println("</tr>");
		        
		        //loops through the resultset to print table entries
		        while(result.next())
		        {
		        	printRow(result, out, 2);
		        }
	        }
	    }
		//prints an appropriate exception error message in place of the table
	    catch (Exception e)
	    {
	    	out.println("<tr><td>Error in servlet: " + e + "</td></tr>");
	    }
	}
	
	protected void printRow(ResultSet set,PrintWriter printer, int numCols)
	{
    	try 
    	{
    		printer.println("<tr>");
    		for (int i = 1; i < numCols + 1; i++)
    		{
    			printer.println("<td class=\"records\">" + set.getString(i) + "</td>");
    		}
    		printer.println("</tr>");
			
		} 
    	catch (SQLException e) 
		{
			printer.println("<tr><td>Error in servlet: " + e + "</td></tr>");
		}
	}
	
	protected void printQueryRow(ResultSet set,PrintWriter printer, int numCols, String table)
	{
    	try 
    	{
    		printer.println("<tr>");
    		printer.println("<td class=\"records\">" + table + "</td>");
    		for (int i = 1; i < numCols + 1; i++)
    		{
    			printer.println("<td class=\"records\">" + set.getString(i) + "</td>");
    		}
    		printer.println("</tr>");
			
		} 
    	catch (SQLException e) 
		{
			printer.println("<tr><td>Error in servlet: " + e + "</td></tr>");
		}
	}
}
